#!/bin/bash

APP=edu
#每日装载备份
# 如果是输入的日期按照取输入日期；如果没输入日期取当前时间的前一天
if [ -n "$2" ] ;then
    do_date=$2
else 
    do_date=`date -d "-1 day" +%F`
fi
dim_user_zip="
set hive.exec.dynamic.partition.mode=nonstrict;
insert overwrite table ${APP}.dim_user_zip partition(dt)
select
    id,
    login_name,
    nick_name,
    real_name,
    phone_num,
    email,
    user_level,
    birthday,
    gender,
    create_time,
    operate_time,
    start_date,
    if(rn=2,date_sub('2022-02-22',1),end_date) end_date,
    if(rn=1,'9999-12-31',date_sub('2022-02-22',1)) dt
from
(
    select
        id,
        login_name,
        nick_name,
        real_name,
        phone_num,
        email,
        user_level,
        birthday,
        gender,
        create_time,
        operate_time,
        start_date,
        end_date,
        row_number() over (partition by id order by start_date desc) rn
    from
    (
        select
            id,
            login_name,
            nick_name,
            real_name,
            phone_num,
            email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            start_date,
            end_date
        from ${APP}.dim_user_zip
        where dt='$do_date'
        union
        select
            id,
            login_name,
            nick_name,
            md5(name) name,
            md5(if(phone_num regexp '^(13[0-9]|14[01456879]|15[0-35-9]|16[2567]|17[0-8]|18[0-9]|19[0-35-9])\\d{8}$',phone_num,null)) phone_num,
            md5(if(email regexp '^[a-zA-Z0-9_-]+@[a-zA-Z0-9_-]+(\\.[a-zA-Z0-9_-]+)+$',email,null)) email,
            user_level,
            birthday,
            gender,
            create_time,
            operate_time,
            '2022-02-22' start_date,
            '9999-12-31' end_date
        from
        (
            select
                data.id,
                data.login_name,
                data.nick_name,
                data.real_name as name,
                data.phone_num,
                data.email,
                data.user_level,
                data.birthday,
                data.gender,
                data.create_time,
                data.operate_time,
                row_number() over (partition by data.id order by ts desc) rn
            from ${APP}.ods_user_info_inc
            where dt='$do_date'
        )t1
        where rn=1
    )t2
)t3;
"
dim_course_full partition="
insert overwrite table ${APP}.dim_course_full partition (dt = '$do_date')
select t1.id,
       t1.course_name,
       t3.id category_id,
       t3.category_name,
       t1.course_slogan,
       t1.course_cover_url,
       t1.subject_id,
       t2.subject_name,
       t1.teacher,
       t1.publisher_id,
       t1.chapter_num,
       t1.origin_price,
       t1.reduce_amount,
       t1.actual_price,
       t1.course_introduce,
       t1.create_time,
       t1.update_time,
       t1.deleted
from t1
left join t2 on t1.subject_id=t2.id
left join t3 on t2.category_id=t3.id;
"
dim_paper_full="
with ${APP}.t1 as (
    select data.id,
           data.paper_title,
           data.course_id,
           data.create_time,
           data.update_time,
           data.publisher_id,
           data.deleted
    from ${APP}.ods_test_paper_inc
    where dt = '$do_date'
      and type = 'bootstrap-insert'
),
     t2 as (
         select id,
                course_name
         from ${APP}.ods_course_info_full
         where dt = '$do_date'
     ),
     t3 as (
         select data.id,
                data.exam_id,
                data.paper_id,
                data.question_id
         from ${APP}.ods_test_exam_question_inc
         where dt = '$do_date'
           and type = 'bootstrap-insert'
     )

insert overwrite table dim_paper_full partition (dt = '2022-02-21')
select t1.id,
       t1.paper_title,
       t1.course_id,
       t2.course_name,
       t3.exam_id,
       t3.question_id,
       t1.create_time,
       t1.update_time,
       t1.publisher_id,
       t1.deleted
from t1
         left join t2 on t1.course_id = t2.id
         left join t3 on t1.id = t3.paper_id;
"
dim_chapter_full ="
insert overwrite table ${APP}.dim_chapter_full partition(dt='$do_date')
select
    chapter.id as id,
    chapter.chapter_name as chapter_name,
    course_id as course_id,
    course_name as course_name,
    video.id as video_id,
    video.video_status as video_status,
    video.video_name as video_name,
    during_sec,
    chapter.course_id,
    course_name,
    course.subject_id,
    subject_name,
    subject.category_id,
    category_name,
    chapter.is_free,
    chapter.create_time,
    chapter.update_time,
    chapter.deleted
from(
     select *
    from ${APP}.ods_chapter_info_full
    where dt = '$do_date'
)
left join (
    select *
    from ${APP}.ods_video_info_full
    where dt = '$do_date'
) video
on chapter.video_id = video.id
left join (
    select *
    from ${APP}.ods_course_info_full
    where dt = '$do_date'
) course
on chapter.course_id = course.id
left join (
    select * 
    from ${APP}.ods_base_subject_info_full
    where dt = '$do_date'
) subject
on course.subject_id = subject.id
left join (
    select *
    from ${APP}.ods_base_category_info_full
    where dt = '$do_date'
) category
on subject.category_id = category.id;
"
case $1 in
"dim_user_zip")
    hive -e "$dim_user_zip"
;;
"dim_course_full")
    hive -e "$dim_province_full"
;;
"dim_paper_full")
    hive -e "$dim_coupon_full"
;;
"dim_chapter_full")
    hive -e "$dim_activity_full"
;;
"all")
    hive -e "$dim_user_zip$dim_course_full$dim_paper_full$dim_chapter_full"
;;
esac
